<?php

namespace App\Model\Dao;

use App\Concern\BaseDao;
use App\Utils\CustomUtils;
use Hyperf\Database\ConnectionInterface;
use Hyperf\Database\Query\Builder;
use Hyperf\DbConnection\Db;
use Hyperf\Utils\Arr;

/**
 * TradeOrderDetailDao
 */
class TradeOrderDetailDao extends BaseDao {

    /**
     * 表名常量
     * 联表的时候通过XxxDao::TABLE获取
     */
    const TABLE = "trade_order_detail";

    // 不支持软删除
    protected $softDelete = null;

    /**
     * 父类中通过静态函数获取表名, 因为不能通过静态常量获取, 这个函数是固定写在子类中的
     */
    public static function table (): string {
        return self::TABLE;
    }

    protected function _buildConnectionTable() : ConnectionInterface{
        return Db::connection();
    }

    public function getCountByTransStatusAndStartTimeAndEndTime($mchId = '', $channelId = '', $transStatus = [], $startTime, $endTime) {
        $main = sprintf("%s AS main", self::TABLE);

        $builder = $this->_buildConnectionTable()->table($main);

        !empty($mchId) && $builder->where("main.mchId", $mchId);
        !empty($channelId) && $builder->where("main.channelId", $channelId);
        $builder->where("main.settleTime", "<", $endTime);
        $builder->where("main.settleTime", ">=", $startTime);

        if (!empty($transStatus)) {
            $builder->whereIn("main.transStatus", $transStatus);
        }
        $builder->selectRaw(join(",", [
            "ifnull(count(main.id), 0) as totalCount",
            ]));
        if (!empty($mchId)) {
            $result = $builder->groupBy("main.mchId")->first();
        } else {
            $result = $builder->groupBy("main.channelId")->first();
        }

        return $result;
    }

    public function getSumByTransStatusAndStartTimeAndEndTime($mchId = '', $channelId = '', $transStatus = [], $startTime, $endTime) {

        $main = sprintf("%s AS main", self::TABLE);

        $builder = $this->_buildConnectionTable()->table($main);

        !empty($mchId) && $builder->where("main.mchId", $mchId);
        !empty($channelId) && $builder->where("main.channelId", $channelId);
        $builder->where("main.settleTime", "<", $endTime);
        $builder->where("main.settleTime", ">=", $startTime);

        if (!empty($transStatus)) {
            $builder->whereIn("main.transStatus", $transStatus);
        }

        $builder->selectRaw(join(",", [
            "ifnull(count(main.id), 0) as totalCount",
            "ifnull(sum(main.mchTransAmount), 0) as mchTransAmount",
            "ifnull(sum(main.channelTransAmount), 0) as channelTransAmount",
            "ifnull(sum(main.settleAmount), 0) as settleAmount",
            "ifnull(sum(main.bondAmount), 0) as bondAmount",
            "ifnull(sum(main.refundAmount), 0) as refundAmount",
            "ifnull(sum(main.refundSettleAmount), 0) as refundSettleAmount",
            "ifnull(sum(main.disAmount), 0) as disAmount",
            "ifnull(sum(main.frozenAmount), 0) as frozenAmount",
            "ifnull(sum(main.thawAmount), 0) as thawAmount",
            "ifnull(sum(main.perFee), 0) as perFee",
            "ifnull(sum(main.serviceFee), 0) as serviceFee",
            "ifnull(sum(main.refundFee), 0) as refundFee",
            "ifnull(sum(main.disFee), 0) as disFee",
            "ifnull(sum(main.disLookIntoFee), 0) as disLookIntoFee",
            "ifnull(sum(main.threeDJudgeFee), 0) as threeDJudgeFee",
            "ifnull(sum(main.threeDFee), 0) as threeDFee",
            "ifnull(sum(main.transFailFee), 0) as transFailFee",
            "ifnull(sum(main.salesCommission1), 0) as salesCommission1",
            "ifnull(sum(main.salesCommission2), 0) as salesCommission2",
        ]));

        if (!empty($mchId)) {
            $result = $builder->groupBy("main.mchId")->first();
        } else {
            $result = $builder->groupBy("main.channelId")->first();
        }

        return $result;
    }

    /**
     * 排序
     *
     * @param Builder $builder
     * @param array   $params
     *
     * @return Builder|mixed
     */
    protected function _buildOrder (Builder $builder, $params = []) {

        // 通过前端提交的参数排序
        if (Arr::exists($params, "orderBy")) {

            [$prop, $order] = $params["orderBy"];

            if (in_array($order, ["asc", "desc"])) {

                //switch ($prop) {
                //    case "yieldMonth":
                //        $builder->orderBy("main.yieldMonth", $order);
                //        break;
                //    default:
                //        break;
                //}
            }
        }

        // 如果友其他固定的排序需求, 在这里写, 例如按照时间降序
        $builder->orderBy("createTime", "desc");

        // 追加父类的排序, 父类按照ID升序, 保证排序结果唯一
        return parent::_buildOrder($builder, $params);
    }

    /**
     * 查询条件
     *
     * @param $params
     *
     * @return Builder
     */
    protected function _buildQueryString ($params) {

        // 本表一律命名为main
        $main = sprintf("%s AS main", self::TABLE);

        $builder = DB::table($main);

        // 通过 Arr::exists 来判断是否存在该键, isset不准确
        (Arr::exists($params, "mchIdList") && !empty($params['mchIdList'])) && $builder->whereIn("main.mchId", $params['mchIdList']);
        Arr::exists($params, "mchId") && $builder->where("main.mchId", "like", "%{$params['mchId']}%");
        Arr::exists($params, "mchNo") && $builder->where("main.mchNo", "like", "%{$params['mchNo']}%");
        Arr::exists($params, "tradeNo") && $builder->where("main.tradeNo", "like", "%{$params['tradeNo']}%");
        Arr::exists($params, "orderNo") && $builder->where("main.orderNo", "like", "%{$params['orderNo']}%");
        Arr::exists($params, "relNo") && $builder->where("main.relNo", "like", "%{$params['relNo']}%");
        Arr::exists($params, "mchUrl") && $builder->where("main.mchUrl", "like", "%{$params['mchUrl']}%");
        Arr::exists($params, "transStatus") && $builder->where("main.transStatus", "=", $params['transStatus']);
        Arr::exists($params, "isSettle") && $builder->where("main.isSettle", "=", $params['isSettle']);
        Arr::exists($params, "isRefund") && $builder->where("main.isRefund", "=", $params['isRefund']);
        Arr::exists($params, "isDis") && $builder->where("main.isDis", "=", $params['isDis']);
        Arr::exists($params, "isFrozen") && $builder->where("main.isFrozen", "=", $params['isFrozen']);
        Arr::exists($params, "isThaw") && $builder->where("main.isThaw", "=", $params['isThaw']);
        Arr::exists($params, "channelId") && $builder->where("main.channelId", "=", $params['channelId']);
        Arr::exists($params, "createTimeRange")  && $builder->whereBetween('main.createTime',$params['createTimeRange']);
        return $builder;
    }

    
}
